package cn.xm.lingshi.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.xm.lingshi.dao.YongHuDao;
import cn.xm.lingshi.pojo.Address;
import cn.xm.lingshi.pojo.User;
import cn.xm.lingshi.pojo.YongHu;
import cn.xm.lingshi.utlis.BaseDao;

/**
 * 用户Dao层
 **/
public class YongHuDaoImpl extends BaseDao implements YongHuDao {

	@Override
	public YongHu dengLu(String zhangHao) {
		String sql = "SELECT userid,username,cardid,birth,phone,account,`password`,usertype,site,userstatus,`date` FROM `user` WHERE account=?";
		Object prm[] = { zhangHao };
		ResultSet rs = null;
		YongHu yh = null;
		try {
			rs = super.executeQuery(sql, prm);
			if (rs != null) {
				while (rs.next()) {
					yh = new YongHu();
					yh.setUserId(rs.getInt("userid"));
					yh.setUserName(rs.getString("username"));
					yh.setCardId(rs.getString("cardid"));
					yh.setBirth(rs.getDate("birth"));
					yh.setPhone(rs.getString("phone"));
					yh.setAccount(rs.getString("account"));
					yh.setPassWord(rs.getString("password"));
					yh.setUserType(rs.getInt("usertype"));
					yh.setSite(rs.getString("site"));
					yh.setUserStatus(rs.getInt("userstatus"));
					yh.setDate(rs.getString("date"));
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return yh;
	}

	@Override
	public int tianJiao(YongHu yongHu) {
		String sql = "INSERT INTO  user(userid,username,cardid,birth,phone,account,password,usertype,site,userstatus,date)  VALUES (?,?,?,?,?,?,?,?,?,?,?)";
		Object prm[] = { yongHu.getUserId(), yongHu.getUserName(),
				yongHu.getCardId(), yongHu.getBirth(), yongHu.getPhone(),
				yongHu.getAccount(), yongHu.getPassWord(),
				yongHu.getUserType(), yongHu.getSite(), yongHu.getUserStatus(),
				yongHu.getDate() };
		int sum = -1;
		try {
			sum = super.executeUpdate(sql, prm);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		super.closeResource(null);
		return sum;
	}

	@Override
	public int shanChu(User yonghu) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int xiuGai(User yongHu) {
		
		
		return 0;
	}

	@Override
	public List<User> chaXun(int jiaoSe, int shuLiang) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int chaXunZhuCe(String zhangHao) {
		String sql = "SELECT COUNT(1)  FROM user WHERE userid=?";
		Object prm[] = { zhangHao };
		ResultSet rs = null;
		int sum = -1;
		try {
			rs = super.executeQuery(sql, prm);
			if (rs != null && rs.next()) {
				sum = rs.getInt(1);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} catch (Exception e) {

			e.printStackTrace();
		}

		return sum;
	}

	@Override
	public int queryTotalRecord(String queryName, int roleId) throws Exception {
		int row = 0;
		// 参数列表
		List<Object> paramsList = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(1) FROM user ");
		sql.append("  where 1=1 ");
		if (queryName != null && !"".equals(queryName)) {
			sql.append(" and username like ?");
			paramsList.add("%" + queryName + "%");
		}
		if (roleId >= 0) {
			sql.append(" and usertype=? ");
			paramsList.add(roleId);
		}
		Object[] params = paramsList.toArray();
		ResultSet rs = super.executeQuery(sql.toString(), params);
		if (rs != null && rs.next()) {
			row = rs.getInt(1);
		}
		super.closeResource(rs);
		return row;
	}

	@Override
	public List<User> queryUserByOffset(int offSet, int pageSize,
			String queryName, int roleId) throws Exception {
		List<User> list = new ArrayList<User>();
		// 参数列表
		List<Object> paramsList = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT userid,username,cardid,birth,phone,account,password,usertype,site,userstatus,date");
		sql.append(" FROM user where 1=1 ");
		if (queryName != null && !"".equals(queryName)) {
			sql.append(" and username like ?");
			paramsList.add("%" + queryName + "%");
		}
		if (roleId >= 0) {
			sql.append(" and usertype=? ");
			paramsList.add(roleId);
		}
		sql.append(" ORDER BY userid DESC LIMIT ?,? ");
		paramsList.add(offSet);
		paramsList.add(pageSize);
		Object[] params = paramsList.toArray();
		ResultSet rs = super.executeQuery(sql.toString(), params);
		while (rs != null && rs.next()) {
			User user = new User();
			user.setUserId(rs.getInt(1));
			user.setUserName(rs.getString(2));
			user.setCardId(rs.getString(3));
			user.setBirth(rs.getDate(4));
			user.setPhone(rs.getString(5));
			user.setAccount(rs.getString(6));
			user.setPassWord(rs.getString(7));
			user.setUserType(rs.getInt(8));
			user.setSite(rs.getString(9));
			user.setUserStatus(rs.getInt(10));
			user.setDate(rs.getDate(11));
			list.add(user);
		}
		super.closeResource(rs);
		return list;
	}

	@Override
	public int addAddress(Address address) throws Exception {
		String sql = "INSERT INTO address (userid,address,reap,phone) VALUES (?,?,?,?)";
		Object[] params = { address.getUserid(), address.getAddress(),
				address.getName(), address.getPhone() };
		return super.executeUpdate(sql, params);
	}

	@Override
	public int modpassword(int userid, String password) throws Exception {
		String sql="UPDATE `user` SET `password` = ? WHERE userid = ?";
		Object[] params={ password,userid};
		return super.executeUpdate(sql, params);
	}

	@Override
	public List<Address> addAddress(int userid) throws Exception {
		String sql="SELECT id,userid,address,city,reap,phone FROM address WHERE userid=?";
		Object[] params={ userid};
		ResultSet rs=super.executeQuery(sql, params);
		List<Address> list=new ArrayList<Address>();
		Address a=null;
		while (rs!=null&&rs.next()) {
			a=new Address();
			a.setUserid(rs.getInt("userid"));
			a.setAddress(rs.getString("address"));
			a.setPhone(rs.getString("phone"));
			a.setName(rs.getString("reap"));
			a.setId(rs.getInt("id"));
			a.setCity(rs.getString("city"));
			list.add(a);
		}
		return list;
	}

}
